
package com.huizhan.web.dao;
 
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.data.domain.*;
import org.springframework.data.jpa.repository.*;
import java.util.List;
import com.huizhan.web.model.*;


@Repository 
public interface ContactDao extends JpaRepository<Contact,Long>
{
     
    /**
     * 分页查赠票记录集合
     * @param name 名称
     * @param phone 手机号
     * @param ticket_Type_Id 票类型ID
     * @param project_Id 项目ID
     * @param ticket_complimentary_Id 类型
     * @param pageable 分页参数
     * @return 赠票记录集合
     */
    @Query(
        value = "SELECT * FROM contact"
            + " WHERE (project_Id =?3 ) "
            + " AND (company LIKE CONCAT('%',?4,'%') OR ?4  = '')"
            + " AND (name LIKE CONCAT('%',?1,'%') OR ?1  = '')"
            + " AND (phone LIKE CONCAT('%',?2,'%') OR ?2  = '' )"
            + " AND unix_timestamp(create_date) between  ?5 and  ?6", 
        countQuery = "SELECT COUNT(1) FROM contact"
            + " WHERE (project_Id =?3 ) "
            + " AND (company LIKE CONCAT('%',?4,'%') OR ?4  = '')"
            + " AND (name LIKE CONCAT('%',?1,'%') OR ?1  = '')"
            + " AND (phone LIKE CONCAT('%',?2,'%') OR ?2  = '' )"
            + " AND unix_timestamp(create_date) between  ?5 and  ?6",  
        nativeQuery = true
    )
    Page<Contact> findByAll(String name,String phone,String project_Id,String company, Long startDate,Long endDate, Pageable  pageable);

    /**
     * 
     * @param phone
     * @param project_Id
     * @return
     */
    Contact findByProjectIdAndPhone(String project_Id,String phone);
    
    /**
     * 
     * @param phone
     * @param project_Id
     * @return
     */
    Contact findByProjectIdAndEmail(String project_Id,String email);
}
